package patientimport;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.bbcare.comm.AppException;
import com.bbcare.comm.ErrorCode;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * 上海一妇婴数据移植处理
 */
public class SynchOldFollow2New {

    public static final String POST_URL_CALL = "http://doctor.bb-care.com:9090/followup/service/patient/patientAdd?sessionId=8a29d32d-c1d4-4d26-a3fb-e7ed0e2646e6";
    public static final String POST_QUES_CALL = "http://doctor.bb-care.com:9090/followup/service/patient/patientTaskContentDataModify?sessionId=8a29d32d-c1d4-4d26-a3fb-e7ed0e2646e6";
    public static final String dbUrl = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/bbcare_cloud";
    public static final String dbUrlOms = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/bbcare_oms";
    public static final String dbUser = "*****";// 需要输入正确用户名
    public static final String dbPwd = "*****";// 需要输入正确密码

    public static Map<String, String> httpPostCall(String parm2, String callUrl) throws Exception {
        try {
            URL url = new URL(callUrl);
            System.out.println("url==" + callUrl);

            // 将url 以 open方法返回的urlConnection 连接强转为HttpURLConnection连接
            // (标识一个url所引用的远程对象连接)
            HttpURLConnection connection = (HttpURLConnection) url.openConnection();// 此时cnnection只是为一个连接对象,待连接中

            // 设置连接输出流为true,默认false (post 请求是以流的方式隐式的传递参数)
            connection.setDoOutput(true);

            // 设置连接输入流为true
            connection.setDoInput(true);

            // 设置请求方式为post
            connection.setRequestMethod("POST");

            // post请求缓存设为false
            connection.setUseCaches(true);

            // 设置该HttpURLConnection实例是否自动执行重定向
            connection.setInstanceFollowRedirects(true);

            // 设置请求头里面的各个属性 (以下为设置内容的类型,设置为经过urlEncoded编码过的from参数)
            // application/x-javascript text/xml->xml数据
            // application/x-javascript->json对象
            // application/x-www-form-urlencoded->表单数据
            // ;charset=utf-8 必须要，不然会出现乱码
            connection.setRequestProperty("Content-Type", "application/json;charset=UTF-8");
            connection.setRequestProperty("Accept-Charset", "UTF-8");
            connection.setRequestProperty("contentType", "UTF-8");
            // connection.setRequestProperty("Cookie", cookie);

            // 建立连接
            // (请求未开始,直到connection.getInputStream()方法调用时才发起,以上各个参数设置需在此方法之前进行)
            connection.connect();

            // 创建输入输出流,用于往连接里面输出携带的参数,(输出内容为?后面的内容)
            OutputStreamWriter dataout = new OutputStreamWriter(connection.getOutputStream());

            // 拼装问卷入参
            /*
             * String parm2 =
             * "{\"data\":{\"followQueueId\":\"12shyfy0000001\",\"opCode\":\"1000\","
             * +
             * "\"opNote\":\"刘江勤医生添加患者苏轼建档信息，加入队列早产儿随访队列\",\"opType\":\"2\",\"userType\":\"1\","
             * +
             * "\"questiones\":{\"Pub1100001\":\"18556571860\",\"Pub1100002\":\"苏轼2\",\"Pub1100003\":\"男\","
             * +
             * "\"Pub1100004\":\"2017-05-10\",\"Pub1100005\":\"2017-05-12\",\"YFY63c2308cf\":\"3233\","
             * +
             * "\"YFY63c9008d0\":\"998111\",\"YFY170620001\":\"055178901235\",\"YFY63d0d08d1\":\"测试1\","
             * +
             * "\"YFY63d7a08d2\":\"测试2\",\"YFY63df708d3\":\"安徽省合肥市长江西路130号\"}}}";
             */

            System.out.println("parm2==[" + parm2 + "]");

            // 将参数输出到连接
            // dataout.writeBytes(parm2);
            dataout.write(parm2);

            // 输出完成后刷新并关闭流
            dataout.flush();
            dataout.close(); // 重要且易忽略步骤 (关闭流,切记!)

            // 连接发起请求,处理服务器响应 (从连接获取到输入流并包装为bufferedReader)
            BufferedReader bf = new BufferedReader(new InputStreamReader(connection.getInputStream(), "UTF-8"));
            String line;
            StringBuilder sb = new StringBuilder(); // 用来存储响应数据

            // 循环读取流,若不到结尾处
            while ((line = bf.readLine()) != null) {
                // sb.append(bf.readLine());
                sb.append(line).append(System.getProperty("line.separator"));
            }
            // ---{"success":true,"data":{"patientId":"14o4ajITLRMI5Zxc4ynK","ptSchemeId":"15pQ0sXjE67NEXSCxBIi"}}
            System.out.println("---" + sb.toString());
            bf.close(); // 重要且易忽略步骤 (关闭流,切记!)
            connection.disconnect(); // 销毁连接

            JSONObject jsonArg = null;
            try {
                jsonArg = JSON.parseObject(sb.toString());
            } catch (Exception ex) {
                // ex.printStackTrace();
                throw (new AppException(ErrorCode.FOLLOWUP_EXCEPTION_TYPE_INPUT_DATA_FORMAT_ERROR, "出参格式错误，非json格式！"));
            }
            boolean retFlag = jsonArg.getBooleanValue("success");
            if (!retFlag) {
                throw (new AppException(ErrorCode.FOLLOWUP_EXCEPTION_TYPE_INPUT_DATA_FORMAT_ERROR,
                        "调用失败：" + jsonArg.getString("msg")));
            }
            Map<String, String> outMap = new HashMap<String, String>();
            if (POST_URL_CALL.equals(callUrl)) {
                JSONObject jsonData = jsonArg.getJSONObject("data");
                if (null == jsonData) {
                    throw (new AppException(ErrorCode.FOLLOWUP_EXCEPTION_TYPE_INPUT_DATA_FORMAT_ERROR,
                            "调用失败：返回无data节点!"));
                }
                String patientId = jsonData.getString("patientId");
                String ptSchemeId = jsonData.getString("ptSchemeId");
                if (StringUtils.isEmpty(patientId) || StringUtils.isEmpty(ptSchemeId)) {
                    throw (new AppException(ErrorCode.FOLLOWUP_EXCEPTION_TYPE_INPUT_DATA_FORMAT_ERROR,
                            "调用失败：返回patientId或ptSchemeId值为空!"));
                }
                outMap.put("patientId", patientId);
                outMap.put("ptSchemeId", ptSchemeId);
            }

            return outMap;
        } catch (Exception e) {
            e.printStackTrace();
            throw (e);
        }
    }

    public static void migrateData() {
        int irow = 0;
        int succNum = 0;
        int errNum = 0;
        // 声明Connection对象
        Connection con = null;
        Connection connOms = null;
        // 驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        // URL指向要访问的数据库名mydata
        String url = dbUrl;
        // MySQL配置时的用户名
        String user = dbUser;
        // MySQL配置时的密码
        String password = dbPwd;

        // taskList
        List<String> oldTaskList = new ArrayList<String>();
        oldTaskList.add("1月龄");
        oldTaskList.add("2月龄");
        oldTaskList.add("3月龄");
        oldTaskList.add("4月龄");
        oldTaskList.add("5月龄");
        oldTaskList.add("6月龄");
        oldTaskList.add("8月龄");
        oldTaskList.add("10月龄");
        oldTaskList.add("12月龄");
        oldTaskList.add("15月龄");
        oldTaskList.add("18月龄");
        oldTaskList.add("24月龄");
        Map<String, String> mapTask = new HashMap<String, String>();
        mapTask.put("1月龄", "1月龄");
        mapTask.put("2月龄", "2月龄");
        mapTask.put("3月龄", "3月龄");
        mapTask.put("4月龄", "4月龄");
        mapTask.put("5月龄", "5月龄");
        mapTask.put("6月龄", "6月龄");
        mapTask.put("8月龄", "8月龄");
        mapTask.put("10月龄", "10月龄");
        mapTask.put("12月龄", "12月龄");
        mapTask.put("15月龄", "15月龄");
        mapTask.put("18月龄", "18月龄");
        mapTask.put("24月龄", "24月龄");

        try {
            // 加载驱动程序
            Class.forName(driver);
            // 1.getConnection()方法，连接MySQL数据库
            con = DriverManager.getConnection(url, user, password);
            if (!con.isClosed()) {
                System.out.println("------Succeeded connecting to the Database!");
            }
            con.setAutoCommit(false);

            connOms = DriverManager.getConnection(dbUrlOms, user, password);
            if (!connOms.isClosed()) {
                System.out.println("------Succeeded connecting to the oms Database!");
            }
            connOms.setAutoCommit(false);
        } catch (SQLException e) {
            // 数据库连接失败异常处理
            e.printStackTrace();
            return;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return;
        }

        // 遍历查询结果集
        try {

            // 2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();

            // 查询新老问题id对应关系
            Map<String, String> idMap = new HashMap<String, String>();
            Map<String, String> radioMap = new HashMap<String, String>();
            String idsql = "select _questionId,new_question_id,radio_flag from tmp_yfyquestion_dict_170621 "
                    + "where _questionId is not null and new_question_id is not null";
            ResultSet idrs = statement.executeQuery(idsql);
            while (idrs.next()) {
                idMap.put(idrs.getString("_questionId"), idrs.getString("new_question_id"));
                radioMap.put(idrs.getString("_questionId"), idrs.getString("radio_flag"));
            }
            System.out.println("----------idMap.size==" + idMap.size());
            if (MapUtils.isEmpty(idMap)) {
                idrs.close();
                con.close();
                return;
            }

            // 患者基本信息
            String _id = null;
            String _accountName = null;
            String _babyName = null;
            String _createTime = null;

            // 一妇婴1.0随访
            String yfyQueueId = "297ebe0e5a2b8db5015a2ba0fd1f0023";// 一妇婴1.0随访队列id
            String quesnaaireName = "建档";// 一妇婴1.0随访任务名称

            // 查询需要移植的患者
            String sql = "select _id,_accountId,_accountName,_babyFirstName,"
                    + " _babyName,_birthday,DATE_FORMAT(_createTime,'%Y-%m-%d %H:%i:%s') as _createTime,"
                    + " _gender,_pregnancyWeek,_userId" + " from tmp_t_followuser_170623 where deal_status='0'";
            System.out.println("----------sql==" + sql);
            // 3.ResultSet类，用来存放获取的结果集
            ResultSet rs = statement.executeQuery(sql);

            String quesSql = "";
            String oldQuesId = "";
            String newQuesId = "";
            String updateSql = "";
            Map<String, String> params = new HashMap<String, String>();
            String quesMapStr = "";
            Statement stmt2 = con.createStatement();
            Statement stmt3 = con.createStatement();
            Statement stmtOms = connOms.createStatement();
            String answer = "";
            String taskSql = "";
            Map<String, String> outMsgMap = new HashMap<String, String>();
            while (rs.next()) {
                params.clear();
                quesMapStr = "";
                irow++;

                _id = rs.getString("_id");
                _accountName = rs.getString("_accountName");
                _babyName = rs.getString("_babyName");
                _createTime = rs.getString("_createTime");
                params.put("opTime", _createTime);
                params.put("followQueueId", "12shyfy0000001");// 队列id写死
                params.put("opCode", "1000");// 建档1000，门诊录入1104
                params.put("opType", "2");// 医生操作
                params.put("userType", "1");// 宝宝
                params.put("opNote", "后台导入患者【" + _babyName + "】建档信息，加入早产儿随访队列");

                // 根据userId查询问卷内容
                quesSql = "SELECT _questionId, _answer FROM "
                        + "t_babyfollowschemerecorddetail WHERE _babyFollowSchemeRecord_id IN (  "
                        + "SELECT _id FROM t_babyfollowschemerecord WHERE _babyFollowSchemeId IN ( "
                        + "SELECT _id FROM t_babyfollowscheme WHERE _followQueueId = '" + yfyQueueId + "'  "
                        + "AND _followUser_id = '" + _id + "')) " + "AND _babyfollowschemerecord_name = '"
                        + quesnaaireName + "' AND _questionId is not null AND _questionId!=''";
                // System.out.println("quesSql==" + quesSql);
                ResultSet resulet2 = stmt2.executeQuery(quesSql);
                StringBuffer quesBuff = new StringBuffer();
                while (resulet2.next()) {
                    answer = "";
                    oldQuesId = resulet2.getString("_questionId");
                    newQuesId = idMap.get(oldQuesId);
                    if (!StringUtils.isEmpty(newQuesId)) {
                        quesBuff.append("\"" + newQuesId + "\"");
                        quesBuff.append(":");
                        answer = resulet2.getString("_answer");

                        if ("Pub1100006".equals(newQuesId) && !StringUtils.isEmpty(answer)) {
                            int dayPre = Integer.parseInt(answer);
                            int week = dayPre / 7;
                            int day = dayPre % 7;
                            answer = "[" + week + "," + day + "]";
                        }

                        if ("[\"F\"]".equals(answer)) {
                            answer = "女";
                        } else if ("[\"N\"]".equals(answer)) {
                            answer = "男";
                        } else if ("[\"男\"]".equals(answer)) {
                            answer = "男";
                        } else if ("[\"女\"]".equals(answer)) {
                            answer = "女";
                        }
                        if ("Pub1100006".equals(newQuesId)) {
                            quesBuff.append("\"" + answer + "\"");
                        } else if (-1 == answer.indexOf("[")) {
                            quesBuff.append("\"" + answer + "\"");
                        } else {
                            quesBuff.append(answer);
                        }
                        quesBuff.append(",");
                    }
                }
                resulet2.close();
                quesMapStr = quesBuff.toString();
                if (!StringUtils.isEmpty(quesMapStr)) {
                    quesMapStr = quesMapStr.substring(0, quesMapStr.length() - 1);
                }
                params.put("quesMapStr", quesMapStr);

                // 输出结果
                System.out.println("---------------begin deal [" + irow + "] record:  " + _id + "\t" + _accountName
                        + "\t" + _babyName + "\t" + params.size());
                try {
                    // 建档
                    outMsgMap.clear();
                    String parm2 = "{\"data\":{\"followQueueId\":\"" + params.get("followQueueId") + "\",\"opCode\":\""
                            + params.get("opCode") + "\"," + "\"opNote\":\"" + params.get("opNote") + "\",\"opType\":\""
                            + params.get("opType") + "\",\"userType\":\"" + params.get("userType") + "\","
                            + "\"questiones\":{" + params.get("quesMapStr") + "}}}";
                    try {
                        outMsgMap = httpPostCall(parm2, POST_URL_CALL);
                        succNum++;
                        updateSql = "update tmp_t_followuser_170623 set deal_status='1',user_id='"
                                + outMsgMap.get("patientId") + "',pt_schemeid='" + outMsgMap.get("ptSchemeId")
                                + "' where _id='" + _id + "'";
                        stmt2.executeUpdate(updateSql);
                        con.commit();
                    } catch (Exception Ex) {
                        errNum++;
                        // ex.printStackTrace();
                        updateSql = "update tmp_t_followuser_170623 set deal_status='2' where _id='" + _id + "'";
                        stmt2.executeUpdate(updateSql);
                        con.commit();
                        continue;
                    }

                    // 门诊任务
                    String taskId = "";
                    int seqNo = -1;
                    String opTime = "";
                    String finishDate = "";
                    for (int i = 0; i < oldTaskList.size(); i++) {
                        System.out.println("---" + oldTaskList.get(i));
                        quesMapStr = "";
                        quesBuff.delete(0, quesBuff.length());

                        quesSql = "SELECT _questionId, _answer, "
                                + "DATE_FORMAT(_createTime,'%Y-%m-%d %H:%i:%s') as opTime,"
                                + "DATE_FORMAT(_createTime,'%Y-%m-%d') as finishDate FROM "
                                + "t_babyfollowschemerecorddetail WHERE _babyFollowSchemeRecord_id IN (  "
                                + "SELECT _id FROM t_babyfollowschemerecord WHERE _babyFollowSchemeId IN ( "
                                + "SELECT _id FROM t_babyfollowscheme WHERE _followQueueId = '" + yfyQueueId + "'  "
                                + "AND _followUser_id = '" + _id + "')) " + "AND _babyfollowschemerecord_name = '"
                                + oldTaskList.get(i) + "' AND _questionId is not null AND _questionId!=''";
                        System.out.println("quesSql==" + quesSql);
                        ResultSet resulet3 = stmt3.executeQuery(quesSql);
                        int chkNull = 0;
                        opTime = "";
                        finishDate = "";
                        while (resulet3.next()) {
                            opTime = resulet3.getString("opTime");
                            finishDate = resulet3.getString("finishDate");

                            answer = "";
                            oldQuesId = resulet3.getString("_questionId");
                            newQuesId = idMap.get(oldQuesId);
                            String chkRadio = radioMap.get(oldQuesId);
                            if (!StringUtils.isEmpty(newQuesId)) {
                                quesBuff.append("\"" + newQuesId + "\"");
                                quesBuff.append(":");
                                answer = resulet3.getString("_answer");
                                if (StringUtils.isEmpty(answer)) {
                                    answer = "";
                                }
                                answer = answer.trim();
                                // 体重，g--->kg
                                if ("bd5ea280282".equals(newQuesId)) {
                                    double weiAnswer = Double.parseDouble(answer);
                                    if (weiAnswer > 100) {
                                        double doubAnswer = weiAnswer / 1000;
                                        answer = "" + doubAnswer;
                                    }
                                }
                                // 单选框答案需要特殊处理，单选格式: ["正常"]--->"正常"
                                if ("1".equals(chkRadio)) {
                                    if (!StringUtils.isEmpty(answer) && '[' == answer.charAt(0)) {
                                        answer = answer.substring(1, answer.length() - 1);
                                        quesBuff.append(answer);
                                    } else {
                                        quesBuff.append("\"" + answer + "\"");
                                    }
                                } else if (!StringUtils.isEmpty(answer) && '[' == answer.charAt(0)) {
                                    // 多选框
                                    System.out.println("answer==" + answer);
                                    answer = answer.replace("\"", "\\\"");
                                    quesBuff.append("\"" + answer + "\"");
                                    System.out.println("\"" + answer + "\"");
                                } else {
                                    quesBuff.append("\"" + answer + "\"");
                                }
                                quesBuff.append(",");
                            }
                            chkNull = 1;
                        }
                        resulet3.close();
                        quesMapStr = quesBuff.toString();
                        if (0 == chkNull || StringUtils.isEmpty(quesMapStr)) {
                            System.out.println("---------" + oldTaskList.get(i) + "---无内容!");
                            continue;
                        }
                        quesMapStr = quesMapStr.substring(0, quesMapStr.length() - 1);
                        outMsgMap.put("quesMapStr", quesMapStr);

                        // 查询新任务id
                        taskSql = "select id,seq_no from pt_task_info where pt_schemeid='" + outMsgMap.get("ptSchemeId")
                                + "' and task_name='" + mapTask.get(oldTaskList.get(i)) + "'";
                        System.out.println("taskSql==" + taskSql);
                        ResultSet taskRs = stmtOms.executeQuery(taskSql);
                        while (taskRs.next()) {
                            taskId = taskRs.getString("id");
                            seqNo = taskRs.getInt("seq_no");
                            break;
                        }
                        taskRs.close();
                        if (StringUtils.isEmpty(taskId)) {
                            continue;
                        }
                        String opNote = "后台导入患者【" + _babyName + "】" + mapTask.get(oldTaskList.get(i)) + "门诊内容";

                        // 调用接口
                        parm2 = "{\"data\":{\"oprInfo\":{\"followQueueId\":\"" + params.get("followQueueId") + "\","
                                + "\"opCode\":\"1001\"," + "\"opNote\":\"" + opNote + "\",\"opType\":\""
                                + params.get("opType") + "\",\"patientId\":\"" + outMsgMap.get("patientId")
                                + "\",\"ptSchemeId\":\"" + outMsgMap.get("ptSchemeId") + "\"}," + "\"questiones\":{"
                                + quesMapStr + "},\"task\":{\"finishDate\":\"" + finishDate + "\"," + "\"opTime\":\""
                                + opTime + "\"," + "\"ptTaskId\":\"" + taskId + "\",\"ptTaskName\":\""
                                + mapTask.get(oldTaskList.get(i)) + "\",\"seqNo\":" + seqNo + ","
                                + "\"ptContentIds\":[{\"contentId\":\"xxxx\",\"templType\":\"0\"}]}}}";
                        String invokeStatus = "";
                        try {
                            httpPostCall(parm2, POST_QUES_CALL);
                            invokeStatus = "1";
                        } catch (Exception ex) {
                            invokeStatus = "2";
                        }
                        String insertSql = "insert into tmp_migrationtask_170625(old_user_id,"
                                + "phone_no,baby_name,deal_status,old_task_name,new_task_name,"
                                + "task_id,new_user_id,pt_schemeId)values" + "('" + _id + "','" + _accountName + "','"
                                + _babyName + "'," + "'" + invokeStatus + "','" + oldTaskList.get(i) + "','"
                                + mapTask.get(oldTaskList.get(i)) + "'," + "'" + taskId + "','"
                                + outMsgMap.get("patientId") + "','" + outMsgMap.get("ptSchemeId") + "')";
                        stmt2.executeUpdate(insertSql);
                        con.commit();
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            rs.close();
            statement.close();
            stmt2.close();
            stmt3.close();
            stmtOms.close();
            con.close();
            connOms.close();
        } catch (SQLException e) {
            // 数据库连接失败异常处理
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            System.out.println("----------total: " + irow);
            System.out.println("----------success: " + succNum);
            System.out.println("----------error: " + errNum);
        }
    }

    // 已建档，只迁移某次随访门诊任务记录
    public static void migrateDataForTask() {
        int irow = 0;
        int succNum = 0;
        int errNum = 0;
        // 声明Connection对象
        Connection con = null;
        Connection connOms = null;
        // 驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        // URL指向要访问的数据库名mydata
        String url = dbUrl;
        // MySQL配置时的用户名
        String user = dbUser;
        // MySQL配置时的密码
        String password = dbPwd;

        // taskList
        List<String> oldTaskList = new ArrayList<String>();
        oldTaskList.add("建档");
        oldTaskList.add("1月龄");
        oldTaskList.add("2月龄");
        oldTaskList.add("3月龄");
        oldTaskList.add("4月龄");
        oldTaskList.add("5月龄");
        oldTaskList.add("6月龄");
        oldTaskList.add("8月龄");
        oldTaskList.add("10月龄");
        oldTaskList.add("12月龄");
        oldTaskList.add("15月龄");
        oldTaskList.add("18月龄");
        oldTaskList.add("24月龄");
        Map<String, String> mapTask = new HashMap<String, String>();
        mapTask.put("建档", "建档");
        mapTask.put("1月龄", "1月龄");
        mapTask.put("2月龄", "2月龄");
        mapTask.put("3月龄", "3月龄");
        mapTask.put("4月龄", "4月龄");
        mapTask.put("5月龄", "5月龄");
        mapTask.put("6月龄", "6月龄");
        mapTask.put("8月龄", "8月龄");
        mapTask.put("10月龄", "10月龄");
        mapTask.put("12月龄", "12月龄");
        mapTask.put("15月龄", "15月龄");
        mapTask.put("18月龄", "18月龄");
        mapTask.put("24月龄", "24月龄");

        try {
            // 加载驱动程序
            Class.forName(driver);
            // 1.getConnection()方法，连接MySQL数据库
            con = DriverManager.getConnection(url, user, password);
            if (!con.isClosed()) {
                System.out.println("------Succeeded connecting to the Database!");
            }
            con.setAutoCommit(false);

            connOms = DriverManager.getConnection(dbUrlOms, user, password);
            if (!connOms.isClosed()) {
                System.out.println("------Succeeded connecting to the oms Database!");
            }
            connOms.setAutoCommit(false);
        } catch (SQLException e) {
            // 数据库连接失败异常处理
            e.printStackTrace();
            return;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return;
        }

        // 遍历查询结果集
        try {

            // 2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();

            // 查询新老问题id对应关系
            Map<String, String> idMap = new HashMap<String, String>();
            Map<String, String> radioMap = new HashMap<String, String>();
            String idsql = "select _questionId,new_question_id,radio_flag from tmp_yfyquestion_dict_170621 "
                    + "where _questionId is not null and new_question_id is not null";
            ResultSet idrs = statement.executeQuery(idsql);
            while (idrs.next()) {
                idMap.put(idrs.getString("_questionId"), idrs.getString("new_question_id"));
                radioMap.put(idrs.getString("_questionId"), idrs.getString("radio_flag"));
            }
            System.out.println("----------idMap.size==" + idMap.size());
            if (MapUtils.isEmpty(idMap)) {
                idrs.close();
                con.close();
                return;
            }

            // 患者基本信息
            String _id = null;
            String _accountName = null;
            String _babyName = null;
            String _createTime = null;

            // 一妇婴1.0随访
            String yfyQueueId = "297ebe0e5a2b8db5015a2ba0fd1f0023";// 一妇婴1.0随访队列id

            // 查询需要移植的患者
            String sql = "select a._accountName,a._babyName,a.task_name,b._id,"
                    + "b._createTime,b.user_id,b.pt_schemeId from tmp_yfytaskinfo_170628 a,"
                    + "tmp_t_followuser_170623 b  where a.deal_status='0' and b.pt_schemeId is not null and "
                    + "a._accountName=b._accountName and a._babyName=b._babyName ";
            System.out.println("----------sql==" + sql);
            // 3.ResultSet类，用来存放获取的结果集
            ResultSet rs = statement.executeQuery(sql);

            String quesSql = "";
            String oldQuesId = "";
            String newQuesId = "";
            String updateSql = "";
            String taskName = "";
            String patientId = "";
            String ptSchemeId = "";
            Map<String, String> params = new HashMap<String, String>();
            String quesMapStr = "";
            Statement stmt2 = con.createStatement();
            Statement stmt3 = con.createStatement();
            Statement stmtOms = connOms.createStatement();
            String answer = "";
            String taskSql = "";
            // Map<String, String> outMsgMap = new HashMap<String, String>();
            while (rs.next()) {
                params.clear();
                quesMapStr = "";
                irow++;

                _accountName = rs.getString("_accountName");
                _babyName = rs.getString("_babyName");
                taskName = rs.getString("task_name");
                _id = rs.getString("_id");
                patientId = rs.getString("user_id");
                ptSchemeId = rs.getString("pt_schemeId");

                _createTime = rs.getString("_createTime");
                params.put("opTime", _createTime);
                params.put("followQueueId", "12shyfy0000001");// 队列id写死
                params.put("opType", "2");// 医生操作
                params.put("userType", "1");// 宝宝
                StringBuffer quesBuff = new StringBuffer();

                // 输出结果
                System.out.println("---------------begin deal [" + irow + "] record:  " + _id + "\t" + _accountName
                        + "\t" + _babyName + "\t" + params.size());
                try {
                    // 门诊任务
                    String taskId = "";
                    int seqNo = -1;
                    String opTime = "";
                    String finishDate = "";
                    {
                        // for (int i = 0; i < oldTaskList.size(); i++) {
                        // System.out.println("---" + oldTaskList.get(i));
                        quesMapStr = "";
                        quesBuff.delete(0, quesBuff.length());

                        quesSql = "SELECT _questionId, _answer, "
                                + "DATE_FORMAT(_createTime,'%Y-%m-%d %H:%i:%s') as opTime,"
                                + "DATE_FORMAT(_createTime,'%Y-%m-%d') as finishDate FROM "
                                + "t_babyfollowschemerecorddetail WHERE _babyFollowSchemeRecord_id IN (  "
                                + "SELECT _id FROM t_babyfollowschemerecord WHERE _babyFollowSchemeId IN ( "
                                + "SELECT _id FROM t_babyfollowscheme WHERE _followQueueId = '" + yfyQueueId + "'  "
                                + "AND _followUser_id = '" + _id + "')) " + "AND _babyfollowschemerecord_name = '"
                                + taskName + "' AND _questionId is not null AND _questionId!=''";
                        System.out.println("quesSql==" + quesSql);
                        ResultSet resulet3 = stmt3.executeQuery(quesSql);
                        int chkNull = 0;
                        opTime = "";
                        finishDate = "";
                        while (resulet3.next()) {
                            opTime = resulet3.getString("opTime");
                            finishDate = resulet3.getString("finishDate");

                            answer = "";
                            oldQuesId = resulet3.getString("_questionId");
                            newQuesId = idMap.get(oldQuesId);
                            String chkRadio = radioMap.get(oldQuesId);
                            if (!StringUtils.isEmpty(newQuesId)) {
                                quesBuff.append("\"" + newQuesId + "\"");
                                quesBuff.append(":");
                                answer = resulet3.getString("_answer");
                                if (StringUtils.isEmpty(answer)) {
                                    answer = "";
                                }
                                answer = answer.trim();

                                if ("Pub1100006".equals(newQuesId) && !StringUtils.isEmpty(answer)) {
                                    int dayPre = Integer.parseInt(answer);
                                    int week = dayPre / 7;
                                    int day = dayPre % 7;
                                    answer = "[" + week + "," + day + "]";
                                }

                                if ("[\"F\"]".equals(answer)) {
                                    answer = "女";
                                } else if ("[\"N\"]".equals(answer)) {
                                    answer = "男";
                                } else if ("[\"男\"]".equals(answer)) {
                                    answer = "男";
                                } else if ("[\"女\"]".equals(answer)) {
                                    answer = "女";
                                }

                                // 体重，g--->kg
                                if ("bd5ea280282".equals(newQuesId)) {
                                    double weiAnswer = Double.parseDouble(answer);
                                    if (weiAnswer > 100) {
                                        double doubAnswer = weiAnswer / 1000;
                                        answer = "" + doubAnswer;
                                    }
                                }
                                // 单选框答案需要特殊处理，单选格式: ["正常"]--->"正常"
                                if ("Pub1100006".equals(newQuesId)) {
                                    quesBuff.append("\"" + answer + "\"");
                                } else if ("1".equals(chkRadio)) {
                                    if (!StringUtils.isEmpty(answer) && '[' == answer.charAt(0)) {
                                        answer = answer.substring(1, answer.length() - 1);
                                        quesBuff.append(answer);
                                    } else {
                                        quesBuff.append("\"" + answer + "\"");
                                    }
                                } else if (!StringUtils.isEmpty(answer) && '[' == answer.charAt(0)) {
                                    // 多选框
                                    System.out.println("answer==" + answer);
                                    answer = answer.replace("\"", "\\\"");
                                    quesBuff.append("\"" + answer + "\"");
                                    System.out.println("\"" + answer + "\"");
                                } else {
                                    quesBuff.append("\"" + answer + "\"");
                                }
                                quesBuff.append(",");
                            }
                            chkNull = 1;
                        }
                        resulet3.close();
                        quesMapStr = quesBuff.toString();
                        if (0 == chkNull || StringUtils.isEmpty(quesMapStr)) {
                            System.out.println("---------" + taskName + "---无内容!");
                            continue;
                        }
                        quesMapStr = quesMapStr.substring(0, quesMapStr.length() - 1);
                        // outMsgMap.put("quesMapStr", quesMapStr);

                        // 查询新任务id
                        taskSql = "select id,seq_no from pt_task_info where pt_schemeid='" + ptSchemeId
                                + "' and task_name='" + mapTask.get(taskName) + "'";
                        System.out.println("taskSql==" + taskSql);
                        ResultSet taskRs = stmtOms.executeQuery(taskSql);
                        while (taskRs.next()) {
                            taskId = taskRs.getString("id");
                            seqNo = taskRs.getInt("seq_no");
                            break;
                        }
                        taskRs.close();
                        if (StringUtils.isEmpty(taskId)) {
                            continue;
                        }
                        String opNote = "后台导入患者【" + _babyName + "】" + mapTask.get(taskName) + "门诊内容";

                        // 调用接口
                        String parm2 = "{\"data\":{\"oprInfo\":{\"followQueueId\":\"" + params.get("followQueueId")
                                + "\"," + "\"opCode\":\"1001\"," + "\"opNote\":\"" + opNote + "\",\"opType\":\""
                                + params.get("opType") + "\",\"patientId\":\"" + patientId + "\",\"ptSchemeId\":\""
                                + ptSchemeId + "\"}," + "\"questiones\":{" + quesMapStr
                                + "},\"task\":{\"finishDate\":\"" + finishDate + "\"," + "\"opTime\":\"" + opTime
                                + "\"," + "\"ptTaskId\":\"" + taskId + "\",\"ptTaskName\":\"" + mapTask.get(taskName)
                                + "\",\"seqNo\":" + seqNo + ","
                                + "\"ptContentIds\":[{\"contentId\":\"xxxx\",\"templType\":\"0\"}]}}}";
                        String invokeStatus = "";
                        try {
                            httpPostCall(parm2, POST_QUES_CALL);
                            invokeStatus = "1";
                        } catch (Exception ex) {
                            invokeStatus = "2";
                        }
                        String insertSql = "insert into tmp_migrationtask_170625(old_user_id,"
                                + "phone_no,baby_name,deal_status,old_task_name,new_task_name,"
                                + "task_id,new_user_id,pt_schemeId)values" + "('" + _id + "','" + _accountName + "','"
                                + _babyName + "'," + "'" + invokeStatus + "','" + taskName + "','"
                                + mapTask.get(taskName) + "'," + "'" + taskId + "','" + patientId + "','" + ptSchemeId
                                + "')";
                        stmt2.executeUpdate(insertSql);
                        updateSql = "update tmp_yfytaskinfo_170628 set deal_status='" + invokeStatus + "' "
                                + "where _accountName='" + _accountName + "' and _babyName='" + _babyName + "'";
                        stmt2.executeUpdate(updateSql);
                        con.commit();
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            rs.close();
            statement.close();
            stmt2.close();
            stmt3.close();
            stmtOms.close();
            con.close();
            connOms.close();
        } catch (SQLException e) {
            // 数据库连接失败异常处理
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            System.out.println("----------total: " + irow);
            System.out.println("----------success: " + succNum);
            System.out.println("----------error: " + errNum);
        }
    }

    public static void main(String[] args) {
        try {
            //migrateData();
            //migrateDataForTask();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
